﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using C1.Win.C1FlexGrid;

namespace TTTTBHYT
{
    public partial class frmKTTHE : Form
    {
        public frmKTTHE()
        {
            InitializeComponent();
        }
        private void GetdataToFrom()
        {
            dbConnection conn;
            conn = new dbConnection();
            string sql = "select * from kiemtrathe order by stt";
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa.DataSource = conn.executeSelectQuery(sql, sqlParameter);
        
        }
        private void DeleteTable()
        {
            dbConnection conn;
            conn = new dbConnection();
            string sql = "delete kiemtrathe";
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa.DataSource = conn.executeInsertQuery(sql, sqlParameter);
        }
        public void importDataFromExcel()
        {
            string FileName = System.IO.Directory.GetCurrentDirectory().ToString() + "\\Imports.xls";
            

            // Connection String to Excel Workbook,Replace DataSource value to point to your excel file location
            string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + FileName + " ;Extended Properties=Excel 8.0";
           
            // Create Connection to Excel Workbook
            using (OleDbConnection connection =
                         new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand
                        ("Select * FROM [Sheet1$]", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString;

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = "Kiemtrathe";
                        bulkCopy.WriteToServer(dr);
                        
                    }
                }
            }
        }
        private void btnImport_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            DeleteTable();
            importDataFromExcel();
            GetdataToFrom();
            FormatGrid();
            Cursor.Current = Cursors.Default;
            MessageBox.Show("Import d÷ liÖu thµnh c«ng");
        }
        private void FormatGrid()
        {
            fa.Cols[1].WidthDisplay = 200;
            fa.Cols[1].StyleDisplay.Font = new Font(".Vntime", 10);
            fa.Cols[4].WidthDisplay = 100;
        }
        private void FormatGrid1()
        {
            fa1.Cols[1].WidthDisplay = 200;
            fa1.Cols[1].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[4].WidthDisplay = 100;
        }
        private void FormatGrid2()
        {
            fa2.Cols[0].WidthDisplay = 200;
            fa2.Cols[0].StyleDisplay.Font = new Font(".Vntime", 10);
           
        }
        private void btnKiemtra_Click(object sender, EventArgs e)
        {
            dbConnection conn;
            conn = new dbConnection();
            Cursor.Current = Cursors.WaitCursor;
            string sql = "select c.*,d.tungay,d.denngay from ( "
            + " select * from kiemtrathe where ltrim(rtrim(mathe)) not in "
            + " (select ltrim(rtrim(sokcb)) "
            + " FROM [MISBHXH_SOTHE_NT].dbo.INTHE_DTL a "
            + " WHERE denngay >= '" + txtNgay.Text.ToString().Substring(6, 4) + "/" + txtNgay.Text.ToString().Substring(3, 2) + "/" + txtNgay.Text.ToString().Substring(0, 2) + "'))"
            + " c left join "
            + " [MISBHXH_SOTHE_NT].dbo.INTHE_DTL d on c.mathe = d.sokcb "
            + " order by c.STT ";

            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa1.DataSource = conn.executeSelectQuery(sql, sqlParameter);
            FormatGrid1();
            for (int i = 1; i < fa1.Rows.Count;i++ )
            {
                if (fa1[i, "ngay_ra"].ToString() == "\0\0\0\0\0\0\0\0\0\0") { fa1[i, "ngay_ra"] = ""; }
                if (fa1[i, "benhkhac"].ToString() == "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0")
                {
                    fa1[i, "benhkhac"] = "";
                }
                if (fa1[i, "khoa"].ToString() == "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0")
                {
                    fa1[i, "khoa"] = "";
                }
                if (fa1[i, "gt_tu"].ToString() == "\0\0\0\0\0\0\0\0\0\0") { fa1[i, "gt_tu"] = ""; }
                if (fa1[i, "gt_den"].ToString() == "\0\0\0\0\0\0\0\0\0\0") { fa1[i, "gt_den"] = ""; }
                if (fa1[i, "diachi"].ToString() == "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0")

                {
                    fa1[i, "diachi"] = "";
                }
                if (fa1[i, "lydo_xt"].ToString() == "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0")
                {
                    fa1[i, "lydo_xt"] = "";
                }
            }
            Cursor.Current = Cursors.Default;
            /*
            + "AND ( "
            + " a.Hethan = 0 OR (a.Hethan = 1 AND a.Ngaygiam IS NOT NULL)) AND a.Tinh_Den='')"
             */
        }

        private void frmKTTHE_Load(object sender, EventArgs e)
        {
            txtNgay.Value  = "01/01/" + DateTime.Now.Year;
        }

        private void btnIn_Click(object sender, EventArgs e)
        {
            // choose file
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.DefaultExt = "xls";
            dlg.FileName = "*.xls";
            if (dlg.ShowDialog() != DialogResult.OK)
                return;
            // save grid as sheet in the book
            FileFlags flags = FileFlags.None;
            fa1.SaveGrid(dlg.FileName, FileFormatEnum.Excel, flags);
        }

        private void fa1_DoubleClick(object sender, EventArgs e)
        {
            dbConnection conn;
            conn = new dbConnection();
            Cursor.Current = Cursors.WaitCursor;
            string sql = "select sobhxh,sokcb,Madvi,tungay,denngay,ma_tinh,ma_bv,ngaycap,lancap "
            + " from [MISBHXH_SOTHE_NT].dbo.INTHE_DTL where sokcb = '" + fa1[fa1.Selection.TopRow, "mathe"] + "'";
                SqlParameter[] sqlParameter = new SqlParameter[0];
                fa2.DataSource = conn.executeSelectQuery(sql, sqlParameter);
                FormatGrid2();
                txtKCB.Text = fa1[fa1.Selection.TopRow, "mathe"].ToString();
            Cursor.Current = Cursors.Default;
  
        }

        private void btnKiemtrathe_Click(object sender, EventArgs e)
        {
            string strCondition="";
            dbConnection conn;
            conn = new dbConnection();
            if (txtKCB.Text != "")
            {
                strCondition = strCondition + "and a.sokcb = '" + txtKCB.Text + "'";
            }
            if (txtHo.Text != "")
            {
                strCondition = strCondition + "and a.ho = '" + txtHo.Text + "'";
            }
            if (txtTen.Text != "")
            {
                strCondition = strCondition + "and a.Ten = '" + txtTen.Text + "'";
            }
            if (txtMadvi.Text != "")
            {
                strCondition = strCondition + "and a.Madvi like '%" + txtMadvi.Text + "%'";
            }
            if (strCondition == "")
            {
                return;
            }
            Cursor.Current = Cursors.WaitCursor;
            string sql = "select a.sobhxh,a.ho,a.ten,a.sokcb,a.Madvi,b.tungay,b.denngay,a.ma_tinh,a.ma_bv,b.ngaycap,b.lancap "
            + " from [MISBHXH_NT].dbo.HOSOCN a left join MISBHXH_SOTHE_NT.dbo.INTHE_DTL b "
            + " on a.sokcb = b.sokcb where 1 = 1 " + strCondition;
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa2.DataSource = conn.executeSelectQuery(sql, sqlParameter);
            FormatGrid2();
            fa2.Cols[1].StyleDisplay.Font = new Font(".Vntime", 10);
            fa2.Cols[2].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[3].StyleDisplay.Font = new Font(".Vntime", 10);
            Cursor.Current = Cursors.Default;
        }

        private void btnkiemtra1_Click(object sender, EventArgs e)
        {
            string strCondition = "";
            dbConnection conn;
            conn = new dbConnection();
            if (txtKCB.Text != "")
            {
                strCondition = strCondition + "and a.sokcb = '" + txtKCB.Text + "'";
            }
            if (txtHo.Text != "")
            {
                strCondition = strCondition + "and a.ho = '" + txtHo.Text + "'";
            }
            if (txtTen.Text != "")
            {
                strCondition = strCondition + "and a.Ten = '" + txtTen.Text + "'";
            }
            if (txtMadvi.Text != "")
            {
                strCondition = strCondition + "and a.Madvi like '%" + txtMadvi.Text + "%'";
            }
            if (strCondition == "")
            {
                return;
            }
            Cursor.Current = Cursors.WaitCursor;
            string sql = "select a.sobhxh,a.ho,a.ten,a.sokcb,a.Madvi,b.tungay,b.denngay,a.ma_tinh,a.ma_bv,b.ngaycap,b.lancap "
            + " from [MISBHXH_BHYT_NT].dbo.HOSOCN a left join MISBHXH_SOTHE_NT.dbo.INTHE_DTL b "
            + " on a.sokcb = b.sokcb where 1 = 1 " + strCondition;
            SqlParameter[] sqlParameter = new SqlParameter[0];
            fa2.DataSource = conn.executeSelectQuery(sql, sqlParameter);
            FormatGrid2();
            fa2.Cols[1].StyleDisplay.Font = new Font(".Vntime", 10);
            fa2.Cols[2].StyleDisplay.Font = new Font(".Vntime", 10);
            fa1.Cols[3].StyleDisplay.Font = new Font(".Vntime", 10);
            Cursor.Current = Cursors.Default;
        }
    }
}
